﻿use QuanLySieuThi
go

create proc sp_DangNhap
	@TenDangNhap nvarchar(200),
	@MatKhau nvarchar(200)
as
	select top(1)* from TaiKhoan 
	where TenDangNhap=@TenDangNhap 
		and MatKhauDangNhap=@MatKhau
go
create proc sp_TaiKhoan_Insert
	@TenDangNhap nVARCHAR(200),
	@MatKhauDangNhap nVARCHAR(200) ,
	@KhoaTaiKhoan BIT ,
	@TenNhanVien nvarchar(200) = '',
	@MaNhanVien nVARCHAR(20)  = '',
	@Administrator BIT ,
	@TenNhomQuyen nvarchar(50)
as
	insert into TaiKhoan values(
		@TenDangNhap,
		@MatKhauDangNhap,
		@KhoaTaiKhoan,
		@TenNhanVien, 
		@MaNhanVien,
		@Administrator,
		@TenNhomQuyen)
go
create proc sp_TaiKhoan_Update
	@TenDangNhap nVARCHAR(200),
	@MatKhauDangNhap nVARCHAR(200) ,
	@KhoaTaiKhoan BIT ,
	@TenNhanVien nvarchar(200) = '',
	@MaNhanVien nVARCHAR(20)  = '',
	@Administrator BIT ,
	@TenNhomQuyen nvarchar(50)
as
	update TaiKhoan set
		MatKhauDangNhap=@MatKhauDangNhap ,
		KhoaTaiKhoan  =@KhoaTaiKhoan,
		TenNhanVien =@TenNhanVien,
		MaNhanVien =@MaNhanVien,
		Administrator  =@Administrator,
		TenNhomQuyen   =@TenNhomQuyen
	where 
		TenDangNhap=@TenDangNhap
go
create proc sp_TaiKhoan_Delete
	@TenDangNhap nvarchar(200)
as
	delete from TaiKhoan where TenDangNhap=@TenDangNhap
go
create proc sp_TaiKhoan_SelectAll
as
	select * from TaiKhoan 
go
create proc sp_TaiKhoan_SelectByKey
	@TenDangNhap nvarchar(200)
as
	select * from TaiKhoan where TenDangNhap=@TenDangNhap
go
--
create proc sp_NhomQuyen_Insert
	@TenNhomQuyen nVARCHAR(50),
	@isDeleted bit
as
	insert into NhomQuyen values(@TenNhomQuyen,@isDeleted)
go
create proc sp_NhomQuyen_Delete
	@TenNhomQuyen nvarchar(50)
as
	delete from ChiTietQuyen where TenNhomQuyen=@TenNhomQuyen
	
	if(exists(select * from NhomQuyen where TenNhomQuyen='Temp'))
		begin
		update TaiKhoan set TenNhomQuyen ='Temp' where TenNhomQuyen=@TenNhomQuyen
		end
	else
		begin
		insert into NhomQuyen values('Temp',0)
		update TaiKhoan set TenNhomQuyen ='Temp' where TenNhomQuyen=@TenNhomQuyen
		end
	delete from NhomQuyen where TenNhomQuyen=@TenNhomQuyen
go
create proc sp_NhomQuyen_Select
as
	select * from NhomQuyen
go
create proc sp_ChiTietQuyen_Insert
	@TenNhomQuyen nvarchar(50) ,
	@TenForm nvarchar(50) ,
	@QuyenThem BIT ,
	@QuyenSua BIT ,
	@QuyenXoa BIT ,
	@QuyenXem BIT
as
	insert into ChiTietQuyen values(@TenNhomQuyen,@TenForm,@QuyenThem,@QuyenSua,@QuyenXoa,@QuyenXem)
go
create proc sp_ChiTietQuyen_SelectByTenNhomQuyen
	@TenNhomQuyen nvarchar(50)
as
	select 
		ChiTietQuyen.ChiTietQuyenID,
		ChiTietQuyen.TenNhomQuyen,
		ChiTietQuyen.TenForm,
		Quyen.Ten,
		ChiTietQuyen.QuyenThem,
		ChiTietQuyen.QuyenSua,
		ChiTietQuyen.QuyenXoa,
		ChiTietQuyen.QuyenXem
	from 
		ChiTietQuyen 
		join
		Quyen
		on 
		ChiTietQuyen.TenForm=Quyen.TenForm
	where
		@TenNhomQuyen=TenNhomQuyen
go
create proc sp_ChiTietQuyen_Update
	@TenNhomQuyen nvarchar(50) ,
	@TenForm nvarchar(50) ,
	@QuyenThem BIT ,
	@QuyenSua BIT ,
	@QuyenXoa BIT ,
	@QuyenXem BIT
as
	update ChiTietQuyen set
	QuyenThem=@QuyenThem,
	QuyenSua=@QuyenSua,
	QuyenXoa=@QuyenXoa,
	QuyenXem=@QuyenXem
	where 
	TenNhomQuyen=@TenNhomQuyen and TenForm=@TenForm
go
create proc sp_Quyen_Select
as
begin
	select * from Quyen
end
go
--------------------------------------------------------------------------------------------------------------------------
--Chi Tiết Đơn Đặt Hàng--
create proc sp_ChiTietDonHangKH_Select
	@MaDonDatHangKH varchar(50)
as
	select * from ChiTietDonHangKH
	where MaDonDatHangKH=@MaDonDatHangKH
go
create proc sp_ChiTietDonHangKH_Insert
	@MaDonDatHangKH	varchar(50)  ,
	@MaHangHoa	varchar(50)  ,
	@TenHangHoa nvarchar(200),
	@SoLuong	float	 ,
	@DonGia	float,
	@PhanTramKhuyenMai float,
	@ThanhTien	float,
	@Thue	float,
	@HanSuDung datetime,
	@GhiChu	nvarchar(200),
	@Deleted	bit
as
	insert into ChiTietDonHangKH values(
		@MaDonDatHangKH	  ,
		@MaHangHoa	  ,
		@TenHangHoa,
		@SoLuong		 ,
		@DonGia	,
		@PhanTramKhuyenMai ,
		@ThanhTien	,
		@Thue	,
		@HanSuDung ,
		@GhiChu	,
		@Deleted)
go
create proc sp_ChiTietDonHangNCC_Select
	@MaDonDatHangNCC	varchar(50)
as
	select * from ChiTietDonHangNCC
	where MaDonDatHangNCC = @MaDonDatHangNCC
go
create proc sp_ChiTietDonHangNCC_Insert
	@MaDonDatHangNCC	varchar(50) ,
	@MaHangHoa	varchar(50),
	@TenHangHoa nvarchar(200),
	@SoLuong	float	,
	@DonGia	float,
	@PhanTramChietKhau float,
	@ThanhTien	float,
	@Thue	float,
	@GhiChu	nvarchar(200),
	@Deleted	bit	
as
	insert into ChiTietDonHangNCC values(
		@MaDonDatHangNCC	 ,
		@MaHangHoa	,
		@TenHangHoa,
		@SoLuong		,
		@DonGia	,
		@PhanTramChietKhau ,
		@ThanhTien	,
		@Thue	,
		@GhiChu	,
		@Deleted)
go		
-- Đơn Đặt Hàng--
create proc sp_DonHangKH_Select
as
	select * from DonDatHangKH
go
create proc sp_DonHangKH_Insert 
	@MaDonDatHangKH	varchar(50) 	 ,
	@NgayDonHang	Datetime,
	@MaKhachHang	varchar(50)	  ,
	@TenKhachHang	nvarchar(200),
	@NoHienThoi	float,
	@TrangThaiDonDatHang	nvarchar(20),
	@NgayNhapDuKien	Datetime,
	@HinhThucThanhToan	nvarchar(20),
	@MaKho	varchar(50),
	@TenKho nvarchar(200),
	@MaNhanVien	varchar(50),
	@TenNhanVien nvarchar(200),
	@MaTienTe	varchar(50),
	@GhiChu	nvarchar(200)	,
	@Deleted	bit
as
	insert into DonDatHangKH values (
		@MaDonDatHangKH 	 ,
		@NgayDonHang	,
		@MaKhachHang		  ,
		@TenKhachHang,
		@NoHienThoi,
		@TrangThaiDonDatHang	,
		@NgayNhapDuKien	,
		@HinhThucThanhToan	,
		@MaKho,
		@TenKho ,
		@MaNhanVien	,
		@TenNhanVien ,
		@MaTienTe	,
		@GhiChu		,
		@Deleted	)
go
create proc sp_DonHangKH_Delete
	@MaDonDatHangKH	varchar(50)
as
	update DonDatHangKH set
	Deleted=1
	where MaDonDatHangKH=@MaDonDatHangKH
go

create proc sp_DonHangNCC_Select
as
	select * from DonDatHangNCC
go
create proc sp_DonHangNCC_Insert 
	@MaDonDatHangNCC	varchar(50),
	@NgayDonHang	Datetime,
	@MaNhaCungCap	varchar(50) ,
	@TenNhaCungCap	nvarchar(200),
	@NoHienThoi	float,
	@TrangThaiDonDatHang	nvarchar(20),
	@NgayNhapDuKien	Datetime,
	@HinhThucThanhToan	nvarchar(20),
	@MaKho	varchar(50),
	@TenKho nvarchar(200),
	@MaNhanVien	varchar(50),
	@TenNhanVien nvarchar(200),
	@MaTienTe	varchar(50),
	@Tygia float,
	@GhiChu	nvarchar(200),
	@Deleted	bit	
as
	insert into DonDatHangNCC values(
		@MaDonDatHangNCC	,
		@NgayDonHang	,
		@MaNhaCungCap	 ,
		@TenNhaCungCap	,
		@NoHienThoi	,
		@TrangThaiDonDatHang	,
		@NgayNhapDuKien	,
		@HinhThucThanhToan	,
		@MaKho	,
		@TenKho ,
		@MaNhanVien	,
		@TenNhanVien ,
		@MaTienTe	,
		@Tygia ,
		@GhiChu	,
		@Deleted)
go
create proc sp_DonHangNCC_Delete
	@MaDonDatHangNCC	varchar(50)
as
	update DonDatHangNCC set
	Deleted=1
	where MaDonDatHangNCC=@MaDonDatHangNCC